﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace PageAdmin.Utils.ORM
{

    /// <summary>
    /// 生成sql语句的帮助类
    /// </summary>
    internal class BuildSqlHelper
    {

        /// <summary>
        /// 获取实体的查询字段组合字符串
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        internal static string GetSelectFields<T>() where T : class, new()
        {
            Type modelType = typeof(T);
            var modelFieldList = AttributeHelper.GetModelFieldMappingList(modelType).Where(c => c.IsFieldMapped == true);
            List<string> fields = new List<string>();
            foreach (var item in modelFieldList)
            {
                fields.Add($"[{item.FieldName}] as [{item.PropertyName}]");
            }
            return string.Join(",", fields);
        }


        /// <summary>
        /// 根据实体获取查询字段sql
        /// </summary>
        /// <returns></returns>
        internal static string CreateSelectSql<T>(string table,string condition = null, string orderBy = null, int? showNumber = null) where T : class, new()
        {
            string fields = GetSelectFields<T>();
            return $"select {FormatShowNumber(showNumber)} {fields} from [{table}] {FormatCondition(condition)} {FormatOrderBy(orderBy)}";
        }


        /// <summary>
        /// 根据实体生成update的sql语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        internal static string CreateUpdateSql<T>(string table,string condition = null) where T : class, new()
        {
            Type modelType = typeof(T);
            var modelFieldList = AttributeHelper.GetModelFieldMappingList(modelType).Where(c => c.IsFieldMapped == true);
            List<string> fields = new List<string>();
            foreach (var item in modelFieldList)
            {
                if (item.FieldName.Equals("id", StringComparison.OrdinalIgnoreCase))
                {
                    continue;
                }
                fields.Add($"[{item.FieldName}]=@{item.PropertyName}");
            }
            return $"update [{table}] set {string.Join(",", fields)} {FormatCondition(condition)}";
        }


        /// <summary>
        /// 根据实体和限制字段生成update的sql语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="fieldLimitList">限制更新的字段</param>
        /// <param name="condition"></param>
        /// <returns></returns>
        internal static string CreateUpdateSql<T>(string table,IEnumerable<string> limitFields, string condition = null) where T : class, new()
        {
            if (limitFields == null)
            {
                return CreateUpdateSql<T>(table,condition);
            }
            HashSet<string> fieldLimitList = new HashSet<string>(limitFields, StringComparer.OrdinalIgnoreCase);
            if (fieldLimitList.Count <= 0)
            {
                return CreateUpdateSql<T>(table,condition);
            }
            Type modelType = typeof(T);
            var modelFieldList = AttributeHelper.GetModelFieldMappingList(modelType).Where(c => c.IsFieldMapped == true);
            List<string> fields = new List<string>();
            foreach (var item in modelFieldList)
            {
                string fieldName = item.FieldName; //真实字段
                if (fieldName.Equals("id", StringComparison.OrdinalIgnoreCase))
                {
                    continue;
                }
                if (fieldLimitList.Contains(fieldName, StringComparer.OrdinalIgnoreCase))
                {
                    fields.Add($"[{fieldName}]=@{item.PropertyName}");
                }
            }
            return $"update [{table}] set {string.Join(",", fields)} {FormatCondition(condition)}";
        }

        /// <summary>
        /// 根据实体生成insert的sql语句
        /// </summary>
        /// <returns></returns>
        internal static string CreateInsertSql<T>(string table,IEnumerable<string> limitFields = null) where T : class, new()
        {
            Type modelType = typeof(T);
            var modelFieldList = AttributeHelper.GetModelFieldMappingList(modelType).Where(c => c.IsFieldMapped == true);
            List<string> fields = new List<string>();
            List<string> paramsField = new List<string>();
            foreach (var item in modelFieldList)
            {
                string fieldName = item.FieldName; //真实字段
                if (fieldName.Equals("id", StringComparison.OrdinalIgnoreCase))
                {
                    continue;
                }
                if (limitFields != null)
                {
                    if (!limitFields.Contains(fieldName, StringComparer.OrdinalIgnoreCase))
                    {
                        continue;
                    }
                }
                fields.Add($"[{fieldName}]");
                paramsField.Add($"@{item.PropertyName}");
            }
            return $"insert into [{table}]({string.Join(",", fields)}) values({string.Join(",", paramsField)})";
        }


        internal static string CreateSelectSql(string table, string selectFields, string condition = null, string orderBy = null, int? showNumber = null)
        {
            if (string.IsNullOrWhiteSpace(table))
            {
                throw new Exception("无效的table参数！");
            }
            condition = FormatCondition(condition);
            orderBy = FormatOrderBy(orderBy);
            selectFields = FormatField(table, selectFields);
            string topStr = FormatShowNumber(showNumber);
            return $"select {topStr} {selectFields} from [{table}] {condition} {orderBy}";
        }

        internal static string CreateSelectSql(string table, string selectFields, string condition, string orderBy, PageInfo pageInfo)
        {
            condition = FormatCondition(condition);
            orderBy = FormatOrderBy(orderBy);
            int currentPage = pageInfo.CurrentPage;
            int pageSize = pageInfo.PageSize;
            selectFields = FormatField(table, selectFields);
            string sql;
            if (string.IsNullOrEmpty(orderBy))
            {
                orderBy = $" order by [{table}].[id] desc";
            }
            //必须sql2005以上
            sql = $"select * from (select row_number() over({orderBy}) [_rownumber],{selectFields} from [{table}] {condition})  [_newtable] where [_newtable].[_rownumber] between {((currentPage - 1) * pageSize + 1)}  and {currentPage * pageSize}";
            return sql;
        }


        /// <summary>
        /// 创建多字段的更新语句
        /// </summary>
        /// <param name="table"></param>
        /// <param name="dbKeyValueList"></param>
        /// <param name="condition"></param>
        /// <param name="outParam"></param>
        /// <returns></returns>
        internal static string CreateUpdateSql(string table, DbKeyValueList dbKeyValueList, string condition, out DbKeyValueList outParam)
        {
            outParam = new DbKeyValueList();
            StringBuilder setString = new StringBuilder();
            int i = 0;
            string paramterKey;
            foreach (var item in dbKeyValueList)
            {
                if (i > 0)
                {
                    setString.Append(",");
                }
                paramterKey = "@updateParamter" + i;
                setString.Append("[" + item.Key + "]=" + paramterKey);
                outParam.Add(paramterKey, item.Value, item.Size);
                i++;
            }
            condition = FormatCondition(condition);
            return $"update  [{table}] set {setString.ToString()} {condition}";
        }

        internal static string FormatOrderBy(string order)
        {
            if (string.IsNullOrEmpty(order))
            {
                return "";
            }
            if (order.StartsWith("order by", StringComparison.OrdinalIgnoreCase))
            {
                return order;
            }
            else
            {
                return $" order by {order}";
            }
        }

        internal static string FormatShowNumber(int? showNumber = null)
        {
            if (showNumber == null || showNumber == 0)
            {
                return "";
            }
            else
            {
                return $" top {showNumber} ";
            }
        }

        internal static string FormatCondition(string condition)
        {
            if (string.IsNullOrEmpty(condition))
            {
                return "";
            }
            if (condition.StartsWith("where", StringComparison.OrdinalIgnoreCase))
            {
                return condition;
            }
            else
            {
                return $" where {condition}";
            }
        }

        /// <summary>
        /// 格式化字段
        /// </summary>
        /// <param name="fields"></param>
        /// <returns></returns>
        internal static string FormatField(string table, string fields)
        {
            if (fields == "*")
            {
                return fields;
            }
            string[] aField = fields.Split(',');
            List<string> list = new List<string>();
            string formatField = null;
            foreach (var field in aField)
            {
                if (field.IndexOf("(") >= 0 || field.IndexOf("[") >= 0 || field.IndexOf("'") >= 0 || field.Trim().IndexOf(" ") >= 0 || field.IndexOf(":") >= 0 || field.IndexOf("-") >= 0 || StringHelper.IsInt(field) || StringHelper.IsDecimal(field))
                {
                    formatField = field;
                }
                else
                {
                    formatField = $"[{StringHelper.FirstCharUpper(field)}]";
                }
                if (formatField.IndexOf(".") <= 0 && formatField.IndexOf("[") == 0)
                {
                    formatField = $"[{table}].{formatField}";
                }
                list.Add(formatField);
            }
            return string.Join(",", list);
        }


    }
}